Here’s an example of a stored procedure with two parameters @Beginning_Date and
@Ending_Date. You know they are parameters because of the @ in front of the variable name. This one comes from the Northwind database
To execute a stored procedure with an input parameter using the Execute SQL task
2.
exec
byRoyalty ?
©1988-2000 Microsoft Corporation. All Rights Reserved.
Go to the SQL
Query Analyzer and type the command shown.
In this example it
doesn’t work because there is a parameter missing as the message indicates.
A correct command would be:
execute
Salesbycategory @category=’...’ where the ... is replaced by a valid category name from
the table.
The
TryNorthwind.adp is an Access Project which is linked to the
Northwind database. There are two types
of icons shown, those which indicate and execute queries and and those which
indicate and accesss stored procedures
You execute the
stored procedure by double clicking it. IF
there is a parameter required, a window comes up and prompts you. IF no parameter is needed the stored
procedure returns the values requested.
To execute a
stored procedure (or a view) in SQL Server, open the query analyzer and type the
execute command. If the name of the
stored procedure has blanks in it, you need double quotes around it. If you need parameters, they need to be
separated by commas and the values need single quotes around them if they are
not numeric. Here’s an example.
execute "Sales by Year" @Beginning_Date='1/12/1996',
@Ending_Date='2/1/1997'